/**
 * A class that handles operations on a Database (singleton).
 * Current implementation uses only Derby Apache, but uses standard SQL Queries
 * 
 * @author remo.lemma@lu.unisi.ch
 */

package ch.unisi.inf.projects.java.uom;

// Libraries
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Set;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.ArrayList;
import java.util.HashMap;

import ch.unisi.inf.projects.java.uom.exceptions.UOMException;
import ch.unisi.inf.projects.java.uom.exceptions.UOMExceptions;
import ch.unisi.inf.projects.java.uom.exceptions.ExceptionHandler;
import ch.unisi.inf.projects.java.uom.elements.Item;
import ch.unisi.inf.projects.java.uom.elements.ItemClass;

public final class Database {
	
	/** Driver name */
	private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";

	/** Protocol name */
	private static final String PROTOCOL = "jdbc:derby:";
			
	/** Database Name */
	private String DBName;
	
	/** Database parser */
	private final Parser parser;
	
	/** Should the database be created if not present */
	private boolean create;
	
	/** Database instance */
	private static Database instance;
	
	/**
	 * Constructor that creates a database handler for the given database
	 * @param DBName the database name
	 * @param create should the database be created or not
	 * @throws Exception handles exceptions for the loaded library
	 */
	private Database(final String DBName, final boolean create)
	{
		this.DBName = DBName;
		this.parser = Parser.instance();
		this.create = create;
	}
	
	/**
	 * Init of the databse for the given database name
	 * @param DBName the name of the database (URL)
	 */
	public static void init (final String DBName)
	{
		init(DBName, false);
	}
	
	/**
	 * Init of the database for the given database name
	 * @param DBName the name of the database (URL)
	 * @param create a flag that is true if the database has to be created
	 */
	public static void init (final String DBName, final boolean create)
	{
		instance = new Database(DBName, create);
	}
	
	/**
	 * Returns the instance of the database class (following the Singleton design pattern)
	 * @return the Database instance
	 * @throws UOMException handles the needed exceptions
	 */
	public static Database instance () throws UOMException
	{
		if (instance == null)
			throw new UOMException(UOMExceptions.DATABASE_INTERNAL_ERROR.code(), "Database Internal Error");
		return instance;
	}
	
	/**
	 * Creates the database
	 * @throws Exception handles the needed exceptions
	 */
	private void dbBootstrap() throws Exception
	{
		Connection conn = DriverManager.getConnection(PROTOCOL + DBName + ";create=true");
		shutdownConn(conn);
		create = false;
		UOM.init();
	}
	
	/**
	 * Establishes a connection with the database
	 * @return the connection
	 * @throws Exception handles exception when the connection fails
	 */
	public Connection connect() throws Exception
	{
		Class.forName(DRIVER).newInstance();
		try {
			return DriverManager.getConnection(PROTOCOL + DBName);
		}
		catch (SQLException se)
		{
			if (ExceptionHandler.dbNotFound(se) && create)
				this.dbBootstrap();
			else {
				throw new UOMException(UOMExceptions.DATABASE_IS_NOT_VALID.code(), "This is not a valid UOM Database (or Database already open)");
			}
			Class.forName(DRIVER).newInstance();
			return DriverManager.getConnection(PROTOCOL + DBName);
		}
	}
	
	/**
	 * Shutdowns the connection to the database
	 * @param conn the connection to be shutdown
	 * @throws SQLException handles the needed exceptions
	 */
	private void shutdownConn (final Connection conn) throws SQLException
	{
		try {
			DriverManager.getConnection(PROTOCOL + ";shutdown=true");
		}
		catch (SQLException se)
		{
			if (ExceptionHandler.dbShutdownCorrectly(se));
		}
		if (conn != null)
			conn.close();
	}
	
	/**
	 * Executes a SQL query (read-only)
	 * @param conn the connection to use to perform the query
	 * @param query the query to be executed
	 * @throws Exception handles query execution failure
	 * @return a ResultSet containing the results of the query execution
	 */
	public ResultSet exec (final Connection conn, final String query) throws Exception
	{
		Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		ResultSet r = stat.executeQuery(query);
		return r;
	}
			
	/**
	 * Executes special kind of SQL queries using executeUpdate()
	 * @param query the query to be executed
	 * @return either row count or 0 
	 * @throws Exception handles the needed exceptions
	 */
	public int execSpecial (final String query) throws Exception
	{
		Connection conn = this.connect();
		final int ret = conn.createStatement().executeUpdate(query);
		shutdownConn(conn);
		return ret;
	}
	
	/**
	 * Execute multiple queries 
	 * @param queries the set of the queries to be executes
	 * @return an array of update counts
	 */
	public int[] executeBatch(final Set<String> queries) throws Exception
	{
		Connection conn = this.connect();
		conn.setAutoCommit(false);
		final Statement stat = conn.createStatement();
		for (String query : queries)
			stat.addBatch(query);
		final int[] num = stat.executeBatch();
		conn.setAutoCommit(true);
		shutdownConn(conn);
		return num;
	}
	
	/**
	 * Gets columns from a table
	 * @param table the table name
	 * @return a Set of strings containing the name of the columns
	 * @throws Exception handles the needed exceptions
	 */
	public Set<String> getColumns (final String table) throws Exception
	{
		final String realTable = parser.dbPrepareTableName(table);
		final Set<String> columns = new HashSet<String>();
		final Connection conn = this.connect();
		final ResultSet rs = conn.getMetaData().getColumns(null, null, realTable.toUpperCase(), "%");
		while (rs.next())
			columns.add(rs.getString("COLUMN_NAME"));
		shutdownConn(conn);
		return columns;
	}
	
	/**
	 * Gets the tables of the database
	 * @return a set containing all the tables of the database
	 * @throws Exception handles the needed exceptions
	 */
	public Set<String> getTables () throws Exception
	{
		final Set<String> tables = new HashSet<String>();
		final Connection conn = this.connect();
		ResultSet r = conn.getMetaData().getTables(null, null, null, new String[] { "TABLE" });
		while (r.next())
			tables.add(r.getString("TABLE_NAME"));
		return tables;
	}
	
	/**
	 * Returns a string representing the UOM-ID of the last inserted Item in the table
	 * @param table the name of the table (not prefixed)
	 * @return a String representing the UOM-ID
	 * @throws Exception handles the needed exceptions
	 */
	public String getLastInserted (final String table) throws Exception
	{
		final String query = "SELECT " + Constants.ITEM_PRIMARY_KEY.value() + " FROM " + parser.dbPrepareTableName(table) + " ORDER BY " + Constants.ITEM_PRIMARY_KEY.value() + " ASC";
		Connection conn = this.connect();
		ResultSet rs = this.exec(conn, query);
		if (rs.getFetchSize() == 0)
			return null;
		String id = null;
		while (rs.next())
			id = rs.getString(1);
		shutdownConn(conn);
		return id;
	}
	
	/**
	 * Adds (creates) new relation table(s)
	 * @param origin the table of origin
	 * @param fields the fields that are relationship tables
	 * @throws Exception handles the needed exceptions
	 */
	private void relTablesAdd (final String origin, Set<String> fields) throws Exception
	{
		final Set<String> queries = new HashSet<String>();
		for (String field : fields) {
			final String relation = parser.dbGetListTypeRelationship(field);
			if (!itemClassIsThere(relation) && !relation.equals(Constants.ITEM_CLASS_STRING.value()))
				throw new UOMException(UOMExceptions.REFERENCE_NOT_EXIST.code(), "Reference does not exist");
			final String query = "CREATE TABLE " + parser.dbPrepareRelTableName(origin, field)
			+ " (ID " + Constants.DB_DEFAULT_TYPE.value() + ", RELID " + Constants.DB_DEFAULT_TYPE.value() + ")";
			queries.add(query);
		}
		this.executeBatch(queries);
	}
	
	/**
	 * Updates/Creates/Deletes the needed relation tables
	 * @param origin the table of origin
	 * @param updates the List of Pair containing the updates (length is always even because there are always oldValue - newValue)
	 * @throws Exception handles the needed exceptions
	 */
	private void relTablesUpdate (final String origin, List<Pair<String,String>> updates) throws Exception
	{
		for (Pair<String,String> update : updates) {
			String query = null;
			final String column = update.getLeft();
			final String newColumn = update.getRight();
			String relationship = parser.dbGetListTypeRelationship(column);
			if (relationship == null)
				relationship = parser.dbGetListTypeRelationship(newColumn);
			final String tableName = parser.dbPrepareRelTableName(origin, column);
			final String newTableName = parser.dbPrepareRelTableName(origin, newColumn);
			if (!itemClassIsThere(relationship) && !relationship.equals(Constants.ITEM_CLASS_STRING.value())) {
				throw new UOMException(UOMExceptions.REFERENCE_NOT_EXIST.code(), "Reference '" + relationship + "' does not exist");
			}
			else if (column == null)
				query = "CREATE TABLE " + newTableName + " (ID " + Constants.DB_DEFAULT_TYPE.value() + ", RELID " + Constants.DB_DEFAULT_TYPE.value() + ")";
			else if (newColumn == null)
				query = "DROP TABLE " + tableName;
			else
				query = "RENAME TABLE " + tableName + " TO " + newTableName;
			this.execSpecial(query);
		}
	}
	
	/**
	 * Deletes the relation tables for the given origin table
	 * @param origin the table of origin
	 * @throws Exception handles the needed exceptions
	 */
	private void relTablesDelete (final String origin) throws Exception
	{
		final Set<String> relTables = this.relTablesGet(origin);
		final Set<String> queries = new HashSet<String>();
		for (String relTable : relTables)
			queries.add("DROP TABLE " + relTable);
		this.executeBatch(queries);
	}
	
	/**
	 * Gets out the set of relational tables for the given origin
	 * @param origin the table of origin
	 * @return a set containing the name of the relation tables
	 * @throws Exception handles the needed exceptions
	 */
	private Set<String> relTablesGet (final String origin) throws Exception
	{
		final Set<String> tables = this.getTables();
		final Set<String> relTables = new HashSet<String>();
		final int length = origin.length() + Constants.ITEM_TABLE_PREFIX.value().length();
		for (String table : tables) {
 			if (table.length() > length && table.substring(0,length).toUpperCase().equals(Constants.ITEM_TABLE_PREFIX.value().toUpperCase() + origin.toUpperCase()))
				relTables.add(table);
		}
		return relTables;
	}
	
	/**
	 * Saves an ItemClass object, creating or updating it's reference in the database
	 * @param itemClass the ItemClass object
	 * @throws Exception handles the needed exceptions
	 */
	public void itemClassSave (final ItemClass itemClass) throws Exception
	{
		if (!itemClass.isModified())
			return;
		else if (itemClassIsThere(itemClass.getName()))
			itemClassUpdate(itemClass);
		else
			itemClassAdd(itemClass);
	}
	
	/**
	 * Checks if the given ItemClass object is already present
	 * @param itemClass the ItemClass object
	 * @return true if it's present, false otherwise
	 * @throws Exception handles the needed exceptions
	 */
	public boolean itemClassIsThere (final String itemClass) throws Exception
	{
		final String query = "SELECT * FROM " + parser.dbPrepareTableName(itemClass);
		final Connection conn = this.connect();
		try {
			this.exec(conn, query);
			shutdownConn(conn);
		}
		catch (SQLException se)
		{
			shutdownConn(conn);
			if (ExceptionHandler.tableNotExist(se))
				return false;
		}
		
		return true;
	}
		
	/**
	 * Adds a new ItemClass
	 * @param itemClass the ItemClass object
	 * @throws Exception handles the needed exceptions
	 */
	private void itemClassAdd (final ItemClass itemClass) throws Exception
	{
		final Set<String> relTables = new HashSet<String>();
		final Set<String> columns = itemClass.getFields().keySet();
		String query = "CREATE TABLE " + parser.dbPrepareTableName(itemClass.getName()) + 
				" (" + Constants.ITEM_PRIMARY_KEY.value() + " bigint generated always as identity primary key,";
		for (String column : columns) {
			if (parser.dbColumnTypeIsList(column))
				relTables.add(column);
			else if (!column.equals(Constants.ITEM_PRIMARY_KEY.value()))
				query += " " + column + " " + Constants.DB_DEFAULT_TYPE.value() + ",";
		}
		query = query.substring(0,query.length() - 1) + ")";
		this.execSpecial(query);
		if (!relTables.isEmpty())
			this.relTablesAdd(itemClass.getName(), relTables);
		// clear eventual updates
		itemClass.getAndClearUpdates();
	}
	
	/**
	 * Updates an existing ItemClass
	 * @param itemClass the ItemClass object
	 * @throws Exception handles the needed exceptions
	 */
	private void itemClassUpdate (final ItemClass itemClass) throws Exception
	{
		final List<Pair<String,String>> updates = itemClass.getAndClearUpdates();
		if (updates.isEmpty())
			return;
		final String query = "ALTER TABLE " + parser.dbPrepareTableName(itemClass.getName()) + " ";
		final Set<String> queries = new HashSet<String>();
		final List<Pair<String,String>> relUpdates = new ArrayList<Pair<String,String>>();

		for (Pair<String,String> update : updates) {
			String realQuery = query;
			final String column = update.getLeft();
			final String newColumn = update.getRight();
			if (column == null && newColumn == null) // should not happen
				continue;
			if (parser.dbColumnTypeIsList(column) || parser.dbColumnTypeIsList(newColumn)) {
				relUpdates.add(update);
				continue;
			}
			else if (column == null) // add column
				realQuery += "ADD COLUMN " + newColumn + " " + Constants.DB_DEFAULT_TYPE.value();
			else if (newColumn == null) // delete column
				realQuery += "DROP COLUMN " + column;
			else
				realQuery = "RENAME COLUMN "  + parser.dbPrepareTableName(itemClass.getName()) + "." + column + " TO " + newColumn;
			queries.add(realQuery);
		}
		if (!queries.isEmpty())
			this.executeBatch(queries);
		if (!relUpdates.isEmpty())
			this.relTablesUpdate(itemClass.getName(), relUpdates);
	}

	/**
	 * Deletes an ItemClass from the database
	 * @param itemClass the ItemClass to be deleted 
	 * @throws Exception handles the needed exceptions
	 */
	public void itemClassDelete (final ItemClass itemClass) throws Exception
	{
		this.execSpecial("DROP TABLE " + parser.dbPrepareTableName(itemClass.getName()));
		this.relTablesDelete(itemClass.getName());
	}
	
	/**
	 * Gets an ItemClass from the database
	 * @param itemClass the name of the ItemClass
	 * @return the ItemClass Object
	 * @throws Exception handles the needed exceptions
	 */
	public ItemClass itemClassGet (final String itemClass) throws Exception
	{
		if (!this.itemClassIsThere(itemClass))
			return null;
		Set<String> columns = this.getColumns(itemClass);
		Set<String> relTables = relTablesGet(itemClass);
		Map<String,String> fields = new HashMap<String,String>();
		final int length = new String(parser.dbPrepareTableName(itemClass)).length() + 1;
		for (String column : columns)
			fields.put(column,null);
		for (String relTable : relTables)
			fields.put(Constants.ITEM_CLASS_LIST.value() + relTable.substring(length), null);
		return new ItemClass(itemClass, fields, false);
	}
	
	/**
	 * Adds new relationship values to the database
	 * @param id the id of the source Item
	 * @param table the original table (ItemClass)
	 * @param relValues the Map of list fields to value
	 * @throws Exception handles the needed exceptions
	 */
	private void relValuesAdd (final String id, final String table, final Map<String,String> relValues) throws Exception
	{
		final Set<String> queries = new HashSet<String>();
		for (String relField : relValues.keySet()) {
			final String tableName = parser.dbPrepareRelTableName(table, relField);
			final String value = relValues.get(relField);
			if (value == null)
				continue;
			final String[] values = value.split(Constants.ITEM_PARSECHAR.value());
			for (String val : values)
				queries.add("INSERT INTO " + tableName + " (ID, RELID) VALUES('" + id + "','" + val + "')");
		}
		this.executeBatch(queries);
	}
	
	/**
	 * Updates relationship values in the database
	 * @param id the source Item id
	 * @param table the origin table (ItemClass)
	 * @param relValues the Map of field to value
	 * @throws Exception handles the needed exceptions
	 */
	private void relValuesUpdate (final String id, final String table, final Map<String,String> relValues) throws Exception
	{
		final Map<String,String> allRelValues = parser.dbParseRelValues(this.relValuesGet(id, table));
		final Map<String,String> updateValues = parser.dbParseRelValues(relValues);
		for (final String rvalue : updateValues.keySet()) {
			if (allRelValues.get(rvalue) != null)
				allRelValues.remove(rvalue);
			else if (updateValues.get(rvalue).equals(""))
				continue;
			else {
				final String[] splits = rvalue.split(Constants.ITEM_PARSECHAR.value());
				String key = new String();
				for (int i = 0; i < splits.length - 2; i ++)
					key += splits[i] + Constants.ITEM_PARSECHAR.value();
				if (splits.length == 2)
					key += splits[1];
				else
					key = key.substring(0,key.length() - 1);
				final String query = "INSERT INTO " + parser.dbPrepareRelTableName(table, key) + "(ID, RELID) VALUES('" + id + "','" + updateValues.get(rvalue) + "')";
				this.execSpecial(query);
			}
		}
		for (String rvalue : allRelValues.keySet()) {
			final String[] splits = rvalue.split(Constants.ITEM_PARSECHAR.value());
			String key = new String();
			for (int i = 0; i < splits.length - 2; i ++)
				key += splits[i] + Constants.ITEM_PARSECHAR.value();
			if (splits.length == 2)
				key += splits[1];
			else
				key = key.substring(0,key.length() - 1);
			if (!relValues.keySet().contains(key))
				continue;
			final String query = "DELETE FROM " + parser.dbPrepareRelTableName(table, key) + " WHERE ID = '" + id + "' AND RELID='" + allRelValues.get(rvalue) + "'";
			this.execSpecial(query);
		}
	}
	
	/**
	 * Deletes the relationship values for the given Item of the given ItemClass
	 * @param id the id of the Item
	 * @param table the ItemClass name
	 * @throws Exception handles the needed exceptions
	 */
	public void relValuesDelete (final String id, final String table) throws Exception
	{
		final Set<String> relTables = relTablesGet(table);
		for (final String relTable : relTables) {
			final String query = "DELETE FROM " + relTable + " WHERE ID='" + id + "'";
			this.execSpecial(query);
		}
	}
	
	/**
	 * Returns a map of relationship values found in all the tables for the given id
	 * @param id the id of the item
	 * @param table the table of the item (its ItemClass)
	 * @return the map of relationships (field to id)
	 * @throws Exception handles the needed exceptions
	 */
	private Map<String,String> relValuesGet (final String id, final String table) throws Exception
	{
		final Map<String,String> values = new HashMap<String,String>();
		final Set<String> relTables = this.relTablesGet(table);
		if (relTables.isEmpty())
			return values;
		final Connection conn = this.connect();
		for (String relTable : relTables) {
			final String query = "SELECT * FROM " + relTable + " WHERE ID = '" + id + "'";
			ResultSet rs = this.exec(conn, query);
			String ids = new String();
			while (rs.next())
				ids += rs.getString("RELID") + Constants.ITEM_PARSECHAR.value();
			if (ids.length() > 0)
				ids = ids.substring(0,ids.length() - 1);
			else
				ids = "";
			values.put(parser.dbParseRelTableName(relTable), ids);
		}
		shutdownConn(conn);	
		return values;
	}
	
	/**
	 * Saves an Item (add or update)
	 * @param item the item to be saved
	 * @throws Exception handles the needed exceptions
	 */
	public void itemSave (final Item item) throws Exception
	{
		if (!item.isModified())
			return;
		else if (item.isNew())
			itemAdd(item);
		else
			itemUpdate(item);
	}
	
	/**
	 * Adds a new Item in the database
	 * @param item the Item to be added
	 * @throws Exception handles the needed exceptions
	 */
	private void itemAdd (final Item item) throws Exception
	{
		final Map<String,String> values = item.getFields();
		final Set<String> fields = values.keySet();
		final String table = item.getItemClass().getName();
		final List<String> fieldValues = new ArrayList<String>();
		final Map<String,String> relValues = new HashMap<String,String>();
		String query = "INSERT INTO " + parser.dbPrepareTableName(table) + " (";
		for (String field : fields) {
			if (field.equals(Constants.ITEM_PRIMARY_KEY.value()))
				continue;
			else if (!parser.dbColumnTypeIsList(field)) {
				query += field + ",";
				fieldValues.add(values.get(field));
			}
			else
				relValues.put(field, values.get(field));
		}
		query = query.substring(0,query.length() - 1) + ") VALUES(";
		for (String value : fieldValues)
			query += "'" + value + "',";
		query = query.substring(0,query.length() - 1) + ")";
		this.execSpecial(query);
		final String id = getLastInserted(table);
		item.setId(id);
		if (!relValues.isEmpty())
			relValuesAdd(id, table, relValues);
		item.getAndClearUpdates();
	}
	
	/**
	 * Updates an Item entry in the database
	 * @param item the Item to be updated
	 * @throws Exception handles the needed exceptions
	 */
	private void itemUpdate (final Item item) throws Exception
	{
		final List<Pair<String,String>> updates = item.getAndClearUpdates();
		if (updates.isEmpty())
			return;
		final String table = item.getItemClass().getName();
		String query = "UPDATE " + parser.dbPrepareTableName(table) + " SET ";
		Map<String,String> relValues = new HashMap<String,String>();
		for (Pair<String,String> update : updates) {
			final String column = update.getLeft();
			final String value = update.getRight();
			if (column == null || value == null)
				continue;
			else if (parser.dbColumnTypeIsList(column))
				relValues.put(column, value);
			else
				query += column + "='" + value + "',";
		}
		query = query.substring(0,query.length() - 1);
		query += " WHERE " + Constants.ITEM_PRIMARY_KEY.value() + "=" + item.getId();
		if (relValues.size() != updates.size()) {
			this.execSpecial(query);
		}
		if (!relValues.isEmpty())
			relValuesUpdate(item.getId(), table, relValues);
	}
	
	/**
	 * Deletes an Item
	 * @param item the Item to be deleted
	 * @throws UOMException handles the needed exceptions
	 */
	public void itemDelete (final Item item) throws Exception
	{
		if (item.isNew())
			return;
		final String id = item.getId();
		final String table = item.getItemClass().getName();
		this.execSpecial("DELETE FROM " + parser.dbPrepareTableName(table) + " WHERE " + Constants.ITEM_PRIMARY_KEY.value() + "=" + id);
		this.relValuesDelete(id,table);
	}
	
	/**
	 * Gets an item from the database
	 * @param id the id of the item
	 * @param itemClass the ItemClass of the Item
	 */
	public Item itemGet (final String id, final ItemClass itemClass, final boolean refresh) throws Exception
	{
		final String table = itemClass.getName();
		if (!this.itemClassIsThere(table))
			return null;
		final Set<String> columns = this.getColumns(table);
		final Map<String,String> values = new HashMap<String,String>(this.relValuesGet(id, table));
		final Connection conn = this.connect();
		final String query = "SELECT * FROM " + parser.dbPrepareTableName(table) + " WHERE " + Constants.ITEM_PRIMARY_KEY.value() + "=" + id;
		final ResultSet rs = this.exec(conn, query);
		if (rs.getFetchSize() == 0)
			return null;
		rs.absolute(1);
		for (final String column : columns) {
			try {
				values.put(column, rs.getString(column));
			}
			catch (SQLException sqle)
			{
				return null;
			}
		}
		shutdownConn(conn);
		final ItemClass c;
		if (refresh)
			c = this.itemClassGet(table);
		else
			c = itemClass;
		return new Item(table.toUpperCase() + Constants.ITEM_PARSECHAR.value() + id, c, values, false);
	}
	
	public Item itemGet (final String id, final ItemClass itemClass) throws Exception
	{
		return itemGet(id,itemClass,false);
	}
	
	/**
	 * Get all ItemClass from the database
	 * @return a set of all ItemClasses
	 * @throws handles the needed exceptions
	 */
	public Set<ItemClass> getItemClasses() throws Exception
	{
		final Set<String> tables = getTables();
		final Set<ItemClass> itemClasses = new HashSet<ItemClass>();
		for (String table : tables) {
			if (table.startsWith(Constants.ITEM_TABLE_PREFIX.value().toUpperCase()) && !table.contains(Constants.ITEM_PARSECHAR.value()))
				itemClasses.add(this.itemClassGet(table.substring(Constants.ITEM_TABLE_PREFIX.value().length())));
		}
		return itemClasses;
	}
	
	/**
	 * Returns a set of all items present for the given ItemClass
	 * @param itemClass the ItemClass
	 * @return the set of Items
	 * @throws Exception handles the needed exceptions
	 */
	public Set<Item> getItemsFromItemClass (final ItemClass itemClass) throws Exception
	{
		final String table = itemClass.getName();
		final Set<Item> items = new HashSet<Item>();
		if (!this.itemClassIsThere(table))
			return items;
		final Set<String> columns = this.getColumns(table);
		final String query = "SELECT * FROM " + parser.dbPrepareTableName(table);
		final Connection conn = this.connect();
		final ResultSet rs = this.exec(conn, query);
		while (rs.next()) {
			final String id = rs.getString(Constants.ITEM_PRIMARY_KEY.value());
			final Map<String,String> values = new HashMap<String,String>();			
			for (final String column : columns)
				values.put(column, rs.getString(column));
			items.add(new Item(table.toUpperCase() + Constants.ITEM_PARSECHAR.value() + id, itemClass, values, false));
		}
		shutdownConn(conn);
		for (Item i : items)
			i.getFields().putAll(this.relValuesGet(i.getId(), table));
		return items;
	}
	
	/**
	 * Gets all the items present in the database
	 * @return the set of items
	 * @throws Exception handles the needed exceptions
	 */
	public Set<Item> getItems() throws Exception
	{
		final Set<ItemClass> itemClasses = this.getItemClasses();
		final Set<Item> items = new HashSet<Item>();
		for (ItemClass c : itemClasses)
			items.addAll(this.getItemsFromItemClass(c));
		return items;
	}
}